﻿namespace Hidistro.SqlDal.Promotions
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities;
    using Hidistro.Entities.Promotions;
    using Hidistro.Membership.Context;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;
    using System.Text;
    using Hidistro.Entities.Members;
    using Hidistro.Entities.Sales;

    public class PromotionDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public int AddPromotion(PromotionInfo promotion, DbTransaction dbTran)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_Promotions(Name, PromoteType, Condition, DiscountValue, StartDate, EndDate, Description) VALUES(@Name, @PromoteType, @Condition, @DiscountValue, @StartDate, @EndDate, @Description); SELECT @@IDENTITY");
            this.database.AddInParameter(sqlStringCommand, "Name", DbType.String, promotion.Name);
            this.database.AddInParameter(sqlStringCommand, "PromoteType", DbType.Int32, (int)promotion.PromoteType);
            this.database.AddInParameter(sqlStringCommand, "Condition", DbType.Currency, promotion.Condition);
            this.database.AddInParameter(sqlStringCommand, "DiscountValue", DbType.Currency, promotion.DiscountValue);
            this.database.AddInParameter(sqlStringCommand, "StartDate", DbType.DateTime, promotion.StartDate);
            this.database.AddInParameter(sqlStringCommand, "EndDate", DbType.DateTime, promotion.EndDate);
            this.database.AddInParameter(sqlStringCommand, "Description", DbType.String, promotion.Description);
            object obj2 = null;
            if (dbTran != null)
            {
                obj2 = this.database.ExecuteScalar(sqlStringCommand, dbTran);
            }
            else
            {
                obj2 = this.database.ExecuteScalar(sqlStringCommand);
            }
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return 0;
        }

        public bool AddPromotionMemberGrades(int activityId, IList<int> memberGrades, DbTransaction dbTran)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("DELETE FROM Hishop_PromotionMemberGrades WHERE ActivityId = {0}", activityId);
            foreach (int num in memberGrades)
            {
                builder.AppendFormat(" INSERT INTO Hishop_PromotionMemberGrades (ActivityId, GradeId) VALUES ({0}, {1})", activityId, num);
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            if (dbTran != null)
            {
                return (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0);
            }
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool AddPromotionProducts(int activityId, string productIds)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("INSERT INTO Hishop_PromotionProducts SELECT @ActivityId, ProductId FROM Hishop_Products WHERE ProductId IN ({0})", productIds) + " AND ProductId NOT IN (SELECT ProductId FROM Hishop_PromotionProducts)");
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool DeletePromotion(int activityId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Promotions WHERE ActivityId = @ActivityId");
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool DeletePromotionProducts(int activityId, int? productId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_PromotionProducts WHERE ActivityId = @ActivityId");
            if (productId.HasValue)
            {
                sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" AND ProductId = {0}", productId.Value);
            }
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool EditPromotion(PromotionInfo promotion, DbTransaction dbTran)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Promotions SET Name = @Name, PromoteType = @PromoteType, Condition = @Condition, DiscountValue = @DiscountValue, StartDate = @StartDate, EndDate = @EndDate, Description = @Description WHERE ActivityId = @ActivityId");
            this.database.AddInParameter(sqlStringCommand, "Name", DbType.String, promotion.Name);
            this.database.AddInParameter(sqlStringCommand, "PromoteType", DbType.Int32, (int)promotion.PromoteType);
            this.database.AddInParameter(sqlStringCommand, "Condition", DbType.Currency, promotion.Condition);
            this.database.AddInParameter(sqlStringCommand, "DiscountValue", DbType.Currency, promotion.DiscountValue);
            this.database.AddInParameter(sqlStringCommand, "StartDate", DbType.DateTime, promotion.StartDate);
            this.database.AddInParameter(sqlStringCommand, "EndDate", DbType.DateTime, promotion.EndDate);
            this.database.AddInParameter(sqlStringCommand, "Description", DbType.String, promotion.Description);
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, promotion.ActivityId);
            if (dbTran != null)
            {
                return (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0);
            }
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public int? GetActiveIdByProduct(int productId)
        {
            int? nullable = null;
            string commandText = string.Format("SELECT ActivityId FROM Hishop_PromotionProducts WHERE ProductId = {0}", productId);
            object obj2 = this.database.ExecuteScalar(CommandType.Text, commandText);
            if (obj2 != null)
            {
                nullable = new int?((int)obj2);
            }
            return nullable;
        }

        public PromotionInfo GetFrontOrNextPromoteInfo(PromotionInfo promote, string type)
        {
            string query = string.Empty;
            if (type == "Next")
            {
                query = "SELECT TOP 1 * FROM Hishop_Promotions WHERE activityId<@activityId AND PromoteType=@PromoteType  ORDER BY activityId DESC";
            }
            else
            {
                query = "SELECT TOP 1 * FROM Hishop_Promotions WHERE activityId>@activityId AND PromoteType=@PromoteType ORDER BY activityId ASC";
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            this.database.AddInParameter(sqlStringCommand, "activityId", DbType.Int32, promote.ActivityId);
            this.database.AddInParameter(sqlStringCommand, "PromoteType", DbType.Int32, Convert.ToInt32(promote.PromoteType));
            PromotionInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulatePromote(reader);
                }
                reader.Close();
            }
            return info;
        }

        public IList<MemberGradeInfo> GetPromoteMemberGrades(int activityId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM aspnet_MemberGrades WHERE GradeId IN (SELECT GradeId FROM Hishop_PromotionMemberGrades WHERE ActivityId = @ActivityId)");
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            IList<MemberGradeInfo> list = new List<MemberGradeInfo>();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    list.Add(DataMapper.PopulateMemberGrade(reader));
                }
            }
            return list;
        }

        public DataTable GetPromotes(Pagination pagination, int promotionType, out int totalPromotes)
        {
            string query = string.Format("SELECT COUNT(*) FROM Hishop_Promotions WHERE 1=1 ", new object[0]);
            if (promotionType != 0)
            {
                query = query + string.Format(" AND PromoteType={0} ", promotionType);
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            totalPromotes = Convert.ToInt32(this.database.ExecuteScalar(sqlStringCommand));
            string str2 = string.Empty;
            StringBuilder builder = new StringBuilder("case Hishop_Promotions.PromoteType");
            builder.AppendFormat(" when 1 then '商品直接打折'", new object[0]);
            builder.AppendFormat(" when 2 then '商品固定金额出售'", new object[0]);
            builder.AppendFormat(" when 3 then '商品减价优惠'", new object[0]);
            builder.AppendFormat(" when 4 then '批发打折'", new object[0]);
            builder.AppendFormat(" when 5 then '买商品赠送礼品'", new object[0]);
            builder.AppendFormat(" when 6 then '商品有买有送'", new object[0]);
            builder.AppendFormat(" when 11 then '订单满额打折'", new object[0]);
            builder.AppendFormat(" when 12 then '订单满额优惠金额'", new object[0]);
            builder.AppendFormat(" when 13 then '混合批发打折'", new object[0]);
            builder.AppendFormat(" when 14 then '混合批发优惠金额'", new object[0]);
            builder.AppendFormat(" when 15 then '订单满额送礼品'", new object[0]);
            builder.AppendFormat(" when 16 then '订单满额送倍数积分'", new object[0]);
            builder.AppendFormat(" when 17 then '订单满额免运费'", new object[0]);
            builder.Append(" end as PromoteTypeName");
            if (pagination.PageIndex == 1)
            {
                str2 = "SELECT TOP 10 *," + builder + " FROM Hishop_Promotions WHERE 1=1 ";
            }
            else
            {
                str2 = string.Format("SELECT TOP {0} *," + builder + " FROM Hishop_Promotions WHERE ActivityId NOT IN (SELECT TOP {1} ActivityId FROM Hishop_Promotions) ", pagination.PageSize, pagination.PageSize * (pagination.PageIndex - 1));
            }
            if (promotionType != 0)
            {
                str2 = str2 + string.Format(" AND PromoteType={0} ", promotionType);
            }
            str2 = str2 + " ORDER BY ActivityId DESC";
            sqlStringCommand = this.database.GetSqlStringCommand(str2);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public PromotionInfo GetPromotion(int activityId)
        {
            PromotionInfo info = null;
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions WHERE ActivityId = @ActivityId; SELECT GradeId FROM Hishop_PromotionMemberGrades WHERE ActivityId = @ActivityId");
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulatePromote(reader);
                }
                reader.NextResult();
                while (reader.Read())
                {
                    info.MemberGradeIds.Add((int)reader["GradeId"]);
                }
            }
            return info;
        }

        public DataTable GetPromotionProducts(int activityId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vw_Hishop_BrowseProductList WHERE ProductId IN (SELECT ProductId FROM Hishop_PromotionProducts WHERE ActivityId = @ActivityId) ORDER BY DisplaySequence");
            this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataTable GetPromotions(bool isProductPromote, bool isWholesale)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions");
            if (isProductPromote)
            {
                if (isWholesale)
                {
                    sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" WHERE PromoteType = {0}", 4);
                }
                else
                {
                    sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" WHERE PromoteType <> {0} AND PromoteType < 10", 4);
                }
            }
            else if (isWholesale)
            {
                sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" WHERE PromoteType = {0} OR PromoteType = {1}", 13, 14);
            }
            else
            {
                sqlStringCommand.CommandText = sqlStringCommand.CommandText + string.Format(" WHERE PromoteType <> {0} AND PromoteType <> {1} AND PromoteType > 10", 13, 14);
            }
            sqlStringCommand.CommandText = sqlStringCommand.CommandText + " ORDER BY ActivityId DESC";
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }
        public PromotionInfo[] GetReducedPromotions(Member member, IList<ShoppingCartItemInfo> lineItems, out decimal reducedAmount)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions WHERE DateDiff(DD, StartDate, getdate()) >= 0 AND DateDiff(DD, EndDate, getdate()) <= 0 AND PromoteType BETWEEN 11 AND 14 AND ActivityId IN (SELECT ActivityId FROM Hishop_PromotionMemberGrades WHERE GradeId = @GradeId)");
            this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, member.GradeId);
            IList<PromotionInfo> list = new List<PromotionInfo>();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    list.Add(DataMapper.PopulatePromote(reader));
                }
            }
            List<PromotionInfo> info = new List<PromotionInfo>();
            reducedAmount = 0;
            for (int i = 0; i < list.Count; i++)
            {
                sqlStringCommand = this.database.GetSqlStringCommand("select ProductId from Hishop_PromotionProducts where ActivityId = @ActivityId");
                this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, list[i].ActivityId);
                IList<int> plist = new List<int>();
                using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
                {
                    while (reader.Read())
                    {
                        plist.Add((int)reader["ProductId"]);
                    }
                }
                if (plist.Count > 0)
                {
                    decimal total = 0;
                    int quantity = 0;
                    List<int> ProductIds = new List<int>();
                    for (int n = 0; n < lineItems.Count; n++)
                    {
                        if (lineItems[n].IsSelect && plist.Contains(lineItems[n].ProductId))
                        {
                            ProductIds.Add(lineItems[n].ProductId);
                            total += lineItems[n].SubTotal;
                            quantity += lineItems[n].Quantity;
                        }
                    }
                    if (total >= list[i].Condition)
                    {
                        list[i].Total = total;
                        list[i].ProductIds = ProductIds.ToArray();
                        int count = info.Count;
                        info.Add(list[i]);
                        switch (list[i].PromoteType)
                        {
                            case PromoteType.FullAmountDiscount:
                                if (total >= list[i].Condition)
                                {
                                    info[count].DiscountValue = total * list[i].DiscountValue;
                                    reducedAmount += total - info[count].DiscountValue;
                                }
                                break;

                            case PromoteType.FullAmountReduced:
                                if (total >= list[i].Condition)
                                {
                                    reducedAmount += list[i].DiscountValue;
                                }
                                break;

                            case PromoteType.FullQuantityDiscount:
                                if (quantity >= ((int)list[i].Condition))
                                {
                                    info[count].DiscountValue = total * list[i].DiscountValue;
                                    reducedAmount += total - info[count].DiscountValue;
                                }
                                break;

                            case PromoteType.FullQuantityReduced:
                                if (quantity >= ((int)list[i].Condition))
                                {
                                    reducedAmount += list[i].DiscountValue;
                                }
                                break;
                        }
                    }
                }
            }
            return info.ToArray();
        }
        public PromotionInfo GetReducedPromotion(Member member, decimal amount, int quantity, out decimal reducedAmount)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions t1 WHERE DateDiff(DD, StartDate, getdate()) >= 0 AND DateDiff(DD, EndDate, getdate()) <= 0 AND PromoteType BETWEEN 11 AND 14 AND ActivityId IN (SELECT ActivityId FROM Hishop_PromotionMemberGrades WHERE GradeId = @GradeId) and (select COUNT(*) from Hishop_PromotionProducts where ActivityId = t1.ActivityId) = 0");
            this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, member.GradeId);
            IList<PromotionInfo> list = new List<PromotionInfo>();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    list.Add(DataMapper.PopulatePromote(reader));
                }
            }
            PromotionInfo info = null;
            reducedAmount = 0M;
            foreach (PromotionInfo info2 in list)
            {
                switch (info2.PromoteType)
                {
                    case PromoteType.FullAmountDiscount:
                        if ((amount >= info2.Condition) && ((amount - (amount * info2.DiscountValue)) > reducedAmount))
                        {
                            reducedAmount = amount - (amount * info2.DiscountValue);
                            info = info2;
                        }
                        break;

                    case PromoteType.FullAmountReduced:
                        if ((amount >= info2.Condition) && (info2.DiscountValue > reducedAmount))
                        {
                            reducedAmount = info2.DiscountValue;
                            info = info2;
                        }
                        break;

                    case PromoteType.FullQuantityDiscount:
                        if ((quantity >= ((int)info2.Condition)) && ((amount - (amount * info2.DiscountValue)) > reducedAmount))
                        {
                            reducedAmount = amount - (amount * info2.DiscountValue);
                            info = info2;
                        }
                        break;

                    case PromoteType.FullQuantityReduced:
                        if ((quantity >= ((int)info2.Condition)) && (info2.DiscountValue > reducedAmount))
                        {
                            reducedAmount = info2.DiscountValue;
                            info = info2;
                        }
                        break;
                }
            }
            return info;
        }
        public PromotionInfo[] GetSendPromotions(Member member, IList<ShoppingCartItemInfo> lineItems, PromoteType promoteType)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions WHERE DateDiff(DD, StartDate, getdate()) >= 0 AND DateDiff(DD, EndDate, getdate()) <= 0 AND PromoteType = @PromoteType AND ActivityId IN (SELECT ActivityId FROM Hishop_PromotionMemberGrades WHERE GradeId = @GradeId) ORDER BY DiscountValue DESC");
            this.database.AddInParameter(sqlStringCommand, "PromoteType", DbType.Int32, (int)promoteType);
            this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, member.GradeId);
            IList<PromotionInfo> list = new List<PromotionInfo>();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    list.Add(DataMapper.PopulatePromote(reader));
                }
            }
            List<PromotionInfo> info = new List<PromotionInfo>();
            for (int i = 0; i < list.Count; i++)
            {
                sqlStringCommand = this.database.GetSqlStringCommand("select ProductId from Hishop_PromotionProducts where ActivityId = @ActivityId");
                this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, list[i].ActivityId);
                IList<int> plist = new List<int>();
                using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
                {
                    while (reader.Read())
                    {
                        plist.Add((int)reader["ProductId"]);
                    }
                }
                if (plist.Count > 0)
                {
                    decimal total = 0;
                    int quantity = 0;
                    List<int> ProductIds = new List<int>();
                    for (int n = 0; n < lineItems.Count; n++)
                    {
                        if (lineItems[n].IsSelect && plist.Contains(lineItems[n].ProductId))
                        {
                            ProductIds.Add(lineItems[n].ProductId);
                            total += lineItems[n].SubTotal;
                            quantity += lineItems[n].Quantity;
                        }
                    }
                    if (total >= list[i].Condition)
                    {
                        list[i].Total = total;
                        list[i].ProductIds = ProductIds.ToArray();
                        info.Add(list[i]);
                    }
                }
            }
            return info.ToArray();
        }
        public PromotionInfo GetSendPromotion(Member member, decimal amount, PromoteType promoteType)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Promotions t1 WHERE DateDiff(DD, StartDate, getdate()) >= 0 AND DateDiff(DD, EndDate, getdate()) <= 0 AND PromoteType = @PromoteType AND Condition <= @Condition AND ActivityId IN (SELECT ActivityId FROM Hishop_PromotionMemberGrades WHERE GradeId = @GradeId) and (select COUNT(*) from Hishop_PromotionProducts where ActivityId = t1.ActivityId) = 0 ORDER BY DiscountValue DESC");
            this.database.AddInParameter(sqlStringCommand, "PromoteType", DbType.Int32, (int)promoteType);
            this.database.AddInParameter(sqlStringCommand, "Condition", DbType.Currency, amount);
            this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, member.GradeId);
            PromotionInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulatePromote(reader);
                }
            }
            return info;
        }
    }
}

